﻿/****************************** Module Header ******************************\
 * Module Name:  IDatabaseHelper.cs
 * Module Version:  1.0.0 (2013-6-5)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database schema infomation.
 * 
 * # Log
 * # 1.0.1 2014-1-23 by sutroon 
 *      1.IDatabaseHelper重构，原来Adv_开头的方法改为SO_开头(SuperOperate)，原来Sql_开头的方法改为SQL_开头，表示返回的是Sql模板字符串。
 *      2.IDatabaseHelper新增获取新建存储过程、新建视图、重命名视图、重命名存储过程等Sql模板字符串的方法
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace UCL.Data
{
    public interface IDatabaseHelper
    {
        /// <summary>
        /// 获取或设置帮助类中当前操作的数据库名称
        /// </summary>
        string DatabaseName { get; set; }

        /// <summary>
        /// 获取或设置帮助类中当前操作的服务器名称
        /// </summary>
        string ServerName { get; set; }

        /// <summary>
        /// 获取或设置帮助类中当前操作的数据表名称
        /// </summary>
        string TableName { get; set; }

        /// <summary>
        /// 获取或设置帮助类中当前操作的数据库连接字符串
        /// </summary>
        string ConnectionString { get; set; }

        /// <summary>
        /// 获取或设置数据集提供程序的名称
        /// </summary>
        string ProviderName { get; }

        /// <summary>
        /// 获取或设置是否调试模式的变量
        /// </summary>
        bool IsDebug { get; set; }

        /// <summary>
        /// 获取或设置帮助类中的消息列表
        /// </summary>
        IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="loginID"></param>
        /// <param name="password"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        string GetConnectionString(string serverName, string loginID, string password, string database = "");

        /// <summary>
        /// 获取通用DbCommand对象
        /// </summary>
        /// <returns></returns>
        DbCommand GetDbCommand();

        /// <summary>
        /// 获取通用DbConnection对象
        /// </summary>
        /// <returns></returns>
        DbConnection GetDbConnection();

        #region So高级操作方法

        /// <summary>
        /// 复制数据库
        /// </summary>
        void SO_CopyDatabase();

        /// <summary>
        /// 备份数据库
        /// </summary>
        void SO_BackupDatabase();

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="structuresql"></param>
        /// <param name="datasql"></param>
        void SO_RebackDatabase(string structuresql, string datasql);

        /// <summary>
        /// 解析Sql文本格式是否正确
        /// <para>正确则返回SUCCESS，否则返回错误字符串</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        string SO_ParseSql(string sql);

        /// <summary>
        /// 获取所有数据库的名称
        /// </summary>
        /// <returns></returns>
        IList<string> SO_GetDatabases();

        /// <summary>
        /// 获取数据库中所有表名称
        /// </summary>
        /// <returns></returns>
        IList<string> SO_GetTables();

        /// <summary>
        /// 获取数据表中所有字段名称
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        IList<string> SO_GetFields(string tableName);

        /// <summary>
        /// 获取数据库中所有视图名称
        /// </summary>
        /// <returns></returns>
        IList<string> SO_GetViews();

        /// <summary>
        /// 获取数据库中所有存储过程名称
        /// </summary>
        /// <returns></returns>
        IList<string> SO_GetProcedures();

        /// <summary>
        /// 丢弃数据表
        /// </summary>
        /// <param name="tablenames"></param>
        void SO_DropTable(params string[] tablenames);

        /// <summary>
        /// 确保数据库存在,如果不存在则创建
        /// </summary>
        /// <param name="dbName"></param>
        /// <remarks>2014-4-9</remarks>
        bool So_EnsureDatabase(string dbName,out string msg);

        /// <summary>
        /// 确保数据表存在,如果不存在则创建
        /// <para>如：SO_EnsureTable("tMyTable","ID int identity(1,1) primary key, Name varchar(32), State smallint not null default 0")</para>
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="fieldsdefinesql"></param>
        /// <remarks>2014-2-21</remarks>
        bool SO_EnsureTable(string tablename, string fieldsdefinesql, out string msg);

        /// <summary>
        /// 确保数据字段存在,如果不存在则创建
        /// <para>如：</para>
        /// <para>DBH.TableName="tMyTable" (如果已经调用过SO_EnsureTable,则默认数据表是SO_EnsureTable传递的数据表参数)</para>
        /// <para>SO_EnsureField("MyFieldName","datetime not null default getdate()");</para>
        /// <para>此方法会默认判断数据表是否存在,因此无需担心数据表不存在而出错</para>
        /// </summary>
        /// <param name="fieldname"></param>
        /// <param name="fielddefinesql"></param>
        /// <param name="tablename"></param>
        /// <remarks>2014-2-21</remarks>
        bool SO_EnsureField(string fieldname, string fielddefinesql, out string msg, string tablename = null);

        /// <summary>
        /// 获取数据库中所有表的结构创建语句
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_GetCreateTableStructures(params string[] tableNames);

        /// <summary>
        /// 获取数据库中所有存储过程的结构创建语句
        /// </summary>
        /// <param name="procNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_GetCreateProcedureStructures(params string[] procNames);

        /// <summary>
        /// 获取数据库中所有视图的结构创建语句
        /// </summary>
        /// <param name="viewNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_GetCreateViewStructures(params string[] viewNames);

        /// <summary>
        /// 将数据表的数据记录导出成Sql格式文本
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_ExportTableDataAsSqls(params string[] tableNames);

        /// <summary>
        /// 将数据表的数据记录导出成Sql格式文本
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        string SO_ExportTableDataAsSql(string tableName, string condition = "");

        /// <summary>
        /// 将数据表的结构定义导出成XML格式
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_ExportTableStructureAsXmls(params string[] tableNames);

        /// <summary>
        /// 将数据表的结构定义导出成标准模型的XML格式
        /// <para>标准模型是适用于市面常见数据库产品的标准模型格式</para>
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        Dictionary<string, string> SO_ExportTableStructureAsStandardXmls(params string[] tableNames);

        /// <summary>
        /// 获取模型更新语句
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        string SO_GetUpdateTableStructureSqlsByXml(System.Xml.Linq.XElement doc, params string[] tableNames);

        /// <summary>
        /// 获取模型创建语句
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        string SO_GetCreateTableStructureSqlsByXml(System.Xml.Linq.XElement doc, params string[] tableNames);

        #endregion

        #region 类型转换方法

        /// <summary>
        /// 将.Net CLR中的数据类型转换为通用数据类型
        /// </summary>
        /// <param name="clrType"></param>
        /// <returns></returns>
        DbType ClrTypeToDbType(Type clrType);

        /// <summary>
        /// 将.Net CLR中的数据库类型转换为通用数据类型的名称
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        string ClrTypeToDbTypeName(Type type);

        /// <summary>
        /// 将通用数据类型名称转换为数据库中的数据类型名称
        /// </summary>
        /// <param name="clrTypeName"></param>
        /// <param name="isNullable"></param>
        /// <returns></returns>
        string DbTypeNameToFieldTypeName(string clrTypeName, bool isNullable = true);

        /// <summary>
        /// 数据库数据类型名称转换为通用数据类型名称
        /// </summary>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        string FieldTypeNameToDbTypeName(string clrTypeName);

        /// <summary>
        /// 数据库数据类型名称转换为.Net CLR数据类型名称
        /// </summary>
        /// <param name="fieldTypeName"></param>
        /// <param name="isNullable"></param>
        /// <returns></returns>
        string FieldTypeToClrTypeName(string fieldTypeName, bool isNullable = true);

        #endregion

        #region 普通CURD等操作方法

        /// <summary>
        /// 执行Sql语句并返回DataSet对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        DataSet ExecuteDataSet(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行Sql语句并返回DataTable对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行分页Sql命令并返回DataTable对象
        /// </summary>
        /// <param name="fromsql"></param>
        /// <param name="orderby"></param>
        /// <param name="fields"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20);

        /// <summary>
        /// 执行Sql语句并返回受影响的函数(无需返回值,用于Insert,Update,Delete等命令)
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        int ExecuteNonQuery(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters);

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters);

        /// <summary>
        /// 执行存储过程且无需返回值
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters);

        /// <summary>
        /// 递归获取分类数据表,返回包含树形层级标识的DataTable
        /// <para>DataTable数据主要用于DropDownList控件</para>
        /// </summary>
        /// <param name="sqlQuery">查询语句,如：select id,name from catalog where parentid={0} and extags='album' order by Ordinal</param>
        /// <param name="parentID">父编号,一般是0</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '_'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        /// <param name="topID"></param>
        /// <param name="topName"></param>
        /// <returns></returns>
        DataTable ExecuteTreeTable(string sqlQuery, string parentID, char paddingChar = '_', int loopCount = 0, string topID = "0", string topName = "请选择");

        /// <summary>
        /// 递归获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>2013-9-5 by sutroon created</para>
        /// <para>2013-9-9 by sutroon 重构，新增父级链表重塑和子集项目关联</para>
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlChildFmt">如：Select ID, Name from tUSerDepartment where ParentID='{0}'</param>
        /// <param name="nodeID">当前节点的值</param>
        /// <param name="sqlfmtItems">如：Select AgentID, Name from tUserMember where DepartmentID='{0}' (DepartmentID是外键，对应部门表的主键)</param>
        /// <param name="sqlfmtParent">如：select ID, Name, ParentID from tUserDepartment where ID='{0}' (需要通过Parent来追溯父级链，并且ParentID必需是第三个查询的字段！)</param>
        /// <returns></returns>
        string ExecuteTreeXml(string sqlChildFmt, string nodeID, string sqlfmtItems = "", string sqlfmtParent = "");

        /// <summary>
        /// 获取单列的所有数据列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="search"></param>
        /// <returns></returns>
        IList<V> GetColumnValues<V>(string search);

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="defv"></param>
        /// <returns></returns>
        object GetValue(string sql, object defv = null);

        /// <summary>
        /// 测试连接是否有效
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        bool TestConnection(out string message);

        /// <summary>
        /// 更新数据表的记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        int Update(string tableName, string fields, string search);

        /// <summary>
        /// 向数据表插入记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        int Insert(string tableName, string fields, string values);

        /// <summary>
        /// 删除数据表记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        int Delete(string tableName, string search);

        /// <summary>
        /// 判断数据表中是否存在符合条件的记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        bool Exists(string tableName, string search, string field = "ID");

        /// <summary>
        /// 查询数据表中符合条件的记录数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        int Count(string tableName, string search, string field = "ID");

        /// <summary>
        /// 查询数据表中的记录,返回DataTable对象
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        DataTable SelectDataTable(string tableName, string search, string fields = "*");

        #endregion

        #region SQL_Operate
        string SQL_SetLimit(string sql, int count);
        string SQL_FormatSql(string sql);
        string Sql_GetAutoIncrementSql();
        string Sql_GetSelectTopNRecordSql(string tableName, string fields = "*", string condition = "", int count = 1);
        string Sql_SetConditionSql(string condition);
        string Sql_SetConditionSql(string where, string orderby);
        #endregion

        #region SQL_Table
        string SQL_GetCreateTableSql();
        string Sql_GetRenameTableSql(string tableName, string newName);
        string Sql_GetDropTableSql(string tablename);


        /// <summary>
        /// 获取用于显示数据表结构的范例Sql
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        string Sql_GetShowStructureSql(string table);

        /// <summary>
        /// 获取用于清空/重置数据表的范例Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        string sql_GetTruncateTableSql(string tableName);

        #endregion

        #region SQL_Procedure

        /// <summary>
        /// 获取用于创建存储过程的范例Sql
        /// </summary>
        /// <returns></returns>
        string SQL_GetCreateProcedureSql();
        string SQL_GetRenameProcedureStructureSql();
        string SQL_GetDropProcedureSql();
        string SQL_GetExecProcedureSql(string itemName);

        #endregion

        #region SQL_View

        string SQL_GetCreateViewSql();
        string SQL_GetRenameViewSql();
        string SQL_GetDropViewSql();
        string SQL_GetExecViewSql(string itemName);
        #endregion

        #region SQL_ObjectList

        string SQL_GetShowDatabasesSql();
        string SQL_GetShowTablesSql();
        string SQL_GetShowFieldsSql(string tableName);
        string SQL_GetShowTableCommonSql(string table);
        string SQL_GetShowProceduresSql();
        string SQL_GetShowViewsSql();
        #endregion



    }
}
